﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using RPSImporter.Infrastructure.Attributes;
using RPSImporter.Infrastructure;
using RPSImporter.Services.Data;
using System.Data.Linq;
using RPSImporter.Services;
using RPSImporter.Properties;
using RPSImporter.Models.Settings;

namespace RPSImporter.Models
{
   [FileTypeName("Persons_FileType")]
   public class Person : ModelBase, IModel
   {
      public Person()
      {
         this.FileSettings = DatabaseSvc.Instance.Settings.PersonFileSettings.First();
      }

      public PersonFileSettings FileSettings { get; set; }

      public bool Import()
      {
         try
         {
            if (this.GetData(this.FileSettings.SocialSecurityNumber) != String.Empty
               && this.GetData(this.FileSettings.FirstName) != String.Empty
               && !this.DB.PERSONs.Any(p => p.SOCIAL_SECURITY_NUMBER == this.GetData(this.FileSettings.SocialSecurityNumber).NoWhiteSpace()
                                            && p.FIRST_NAME == this.GetData(this.FileSettings.FirstName)
                                            && p.BADGE_NUMBER == this.GetData(this.FileSettings.BadgeNumber)))
            {
               ContactDetail contactDetail = new ContactDetail();
               contactDetail.Address1 = this.GetData(this.FileSettings.Address1);
               contactDetail.CITY = this.DB.CITies.Where(c => c.NAME == this.GetData(this.FileSettings.City)).FirstOrDefault();
               contactDetail.ZipCode = this.GetData(this.FileSettings.ZipCode);
               contactDetail.Phone = this.GetData(this.FileSettings.Phone);
               this.DB.ContactDetails.InsertOnSubmit(contactDetail);
               this.DB.SubmitChanges();

               ContactDetail contactDetailWork = new ContactDetail();
               contactDetailWork.Address1 = this.DB.ENTERPRISEs.Where(e => e.NAME == this.GetData(this.FileSettings.Enterprise)).Select(e => e.ADDRESS1).FirstOrDefault();
               contactDetailWork.CITY = this.DB.ENTERPRISEs.Where(e => e.NAME == this.GetData(this.FileSettings.Enterprise)).Select(e => e.CITY).FirstOrDefault();
               contactDetailWork.ZipCode = this.DB.ENTERPRISEs.Where(e => e.NAME == this.GetData(this.FileSettings.Enterprise)).Select(e => e.US_ZIPCODE).FirstOrDefault();

               this.DB.ContactDetails.InsertOnSubmit(contactDetailWork);
               this.DB.SubmitChanges();

               DETAIL personDetail = new DETAIL();
               personDetail.PersonnelType = this.DB.PersonnelTypes.Where(t => t.Name == this.GetData(this.FileSettings.TypePersonel)).FirstOrDefault();
               personDetail.ENTERPRISE = this.DB.ENTERPRISEs.Where(e => e.NAME == this.GetData(this.FileSettings.Enterprise)).FirstOrDefault();
               personDetail.OCCUPATION_TYPE = this.GetData(this.FileSettings.Occupation);
               personDetail.STARTING_DATE = this.GetDataAsDate(this.FileSettings.StartingDate);
               personDetail.PROFFESION = this.GetData(this.FileSettings.Proffesion);
               personDetail.Qualification = this.DB.Qualifications.Where(q => q.QualificationName == this.GetData(this.FileSettings.Qualification)).FirstOrDefault();
               personDetail.HIREMENT_DATE = this.GetDataAsDate(this.FileSettings.HirementDate);

               this.DB.DETAILs.InsertOnSubmit(personDetail);
               this.DB.SubmitChanges();

               WORKING_LOCATION workLocation = new WORKING_LOCATION();
               workLocation.idCompany = this.DB.COMPANies.Where(o => o.NAME == this.GetData(this.FileSettings.Company)).Select(o => o.ID).FirstOrDefault();
               this.DB.WORKING_LOCATIONs.InsertOnSubmit(workLocation);
               this.DB.SubmitChanges();

               PERSON person = new PERSON();
               person.FIRST_NAME = this.GetData(this.FileSettings.FirstName);
               person.LAST_NAME = this.GetData(this.FileSettings.LastName);
               person.GENDER = this.GetData(this.FileSettings.Gender); // M or F
               person.MAIDEN_NAME = this.GetData(this.FileSettings.MaidenName);
               person.BADGE_NUMBER = this.GetData(this.FileSettings.BadgeNumber);
               person.AGE = (this.GetData(this.FileSettings.Age) != String.Empty) ? (int?)Convert.ToInt32(this.GetData(this.FileSettings.Age)) : null;
               person.BIRTHDATE = this.GetDataAsDate(this.FileSettings.BirthDate);
               person.CIVIL_STATE = this.GetData(this.FileSettings.CivilState);
               person.LANGUAGE = this.GetData(this.FileSettings.Language);
               person.AGE_CLASS = this.GetData(this.FileSettings.Age) != String.Empty ? (from ac in this.DB.age_groups
                                                                                         where ac.min <= (int?)Convert.ToInt32(this.GetData(this.FileSettings.Age))
                                                                               && ac.max > (int?)Convert.ToInt32(this.GetData(this.FileSettings.Age))
                                                                                         select ac.name).FirstOrDefault() : String.Empty;
               person.NATIONALITY = this.GetData(this.FileSettings.Nationality);
               person.personnelCategorieSante = this.GetData(this.FileSettings.CategorieSante);
               person.SOCIAL_SECURITY_NUMBER = this.GetData(this.FileSettings.SocialSecurityNumber).NoWhiteSpace();
               person.ControlKey = this.GetData(this.FileSettings.ControlKey);
               person.COMPANY = this.GetData(this.FileSettings.Company);
               person.ENTERPRISE = (from e in this.DB.ENTERPRISEs
                                    where e.NAME == this.GetData(this.FileSettings.Enterprise)
                                    select e).FirstOrDefault();
               if (DatabaseSvc.Instance.Settings.GeneralSettings.FirstOrDefault().IsMultiColumnPersonImport)
               {
                  var hierarchy = (from o in this.DB.HIERARCHies
                                   let firstID = this.DB.HIERARCHies.FirstOrDefault(h => h.COMPANY.NAME == this.GetData(this.FileSettings.Company) && h.type == 1).HIERARCHY_ID
                                   let secondID = this.DB.HIERARCHies.FirstOrDefault(h => h.COMPANY.NAME == this.GetData(this.FileSettings.Enterprise) && h.type == 2 && h.PARENT_ID == firstID).HIERARCHY_ID
                                   let thirdID = this.DB.HIERARCHies.FirstOrDefault(h => h.COMPANY.NAME == this.GetData(this.FileSettings.Department) && h.type == 3 && h.PARENT_ID == secondID).HIERARCHY_ID
                                   let forthID = this.DB.HIERARCHies.FirstOrDefault(h => h.COMPANY.NAME == this.GetData(this.FileSettings.Sector) && o.type == 3 && h.PARENT_ID == thirdID).HIERARCHY_ID
                                   where o.HIERARCHY_ID == forthID
                                   select o).FirstOrDefault();
                  person.HIERARCHY = hierarchy;
               }
               else
               {
                  person.HIERARCHY = this.GetData(this.FileSettings.Hierarchy) != String.Empty ?
                                                   (from h in this.DB.HIERARCHies
                                                    where h.NAME == this.GetData(this.FileSettings.Hierarchy)
                                                    select h).FirstOrDefault() : null;
               }
               person.ContactDetail = contactDetail;
               person.DETAIL = personDetail;

               person.COMPANY_ID = (from c in this.DB.COMPANies
                                    where c.NAME == this.GetData(this.FileSettings.Company)
                                    select c.ID).FirstOrDefault();
               person.COUNTRY = (from c in this.DB.COUNTRies
                                 where c.NAME == this.GetData(this.FileSettings.Country)
                                 select c).FirstOrDefault();

               person.idContactWorkingLocation = contactDetailWork.id;

               person.idGender = (from c in this.DB.ConfigDropDownLists
                                  where c.Name_fr == this.GetData(this.FileSettings.Gender) && c.ddl_id == 6
                                  select c.id).FirstOrDefault();

               person.idLanguage = (from l in this.DB.Languages
                                    where l.Name == this.GetData(this.FileSettings.Language)
                                    select l.id).FirstOrDefault();

               person.idMaritalStatus = this.DB.ConfigDropDownLists.
                                          Where(o => o.Name_fr == this.GetData(this.FileSettings.CivilState) && o.ddl_id == 5).
                                          Select(o => o.id).FirstOrDefault();

               person.parent_id = this.DB.HIERARCHies.
                                  Where(o => o.Code == this.GetData(this.FileSettings.Hierarchy)).
                                  Select(o => o.PARENT_ID).FirstOrDefault();

               var typePersonel = this.DB.PersonnelTypes.
                                       Where(o => o.Name == this.GetData(this.FileSettings.TypePersonel)).
                                       Select(o => o.Name).FirstOrDefault();
               person.PERSONNEL_TYPE = typePersonel == String.Empty ? "Interne" : typePersonel;
               person.SOCIAL_WORKER = this.GetData(this.FileSettings.SocialWorker);
               person.working_hour = (from o in this.DB.working_hours
                                      where o.name == this.GetData(this.FileSettings.WorkingHoursName)
                                      select o).FirstOrDefault();
               var workingHoursID = (from o in this.DB.working_hours
                                     where o.name == this.GetData(this.FileSettings.WorkingHoursName)
                                     select o.id_working_hours).FirstOrDefault();
               person.WorkingHoursID = workingHoursID == 0 ? new working_hour()
                                        {
                                           code = this.GetData(this.FileSettings.WorkingHoursCode),
                                           name = this.GetData(this.FileSettings.WorkingHoursName),
                                        }.id_working_hours : workingHoursID;

               person.WorkingLocationID = workLocation.WORKING_LOCATION_ID;
               this.DB.PERSONs.InsertOnSubmit(person);
               this.DB.SubmitChanges();

               string firstLevel = this.GetData(this.FileSettings.FirstLevel);
               string secondLevel = this.GetData(this.FileSettings.SecondLevel);
            }
            else
            {
               PERSON person = this.DB.PERSONs.FirstOrDefault(p => p.SOCIAL_SECURITY_NUMBER == this.GetData(this.FileSettings.SocialSecurityNumber).NoWhiteSpace()
                                                   && p.FIRST_NAME == this.GetData(this.FileSettings.FirstName)
                                                   && p.BADGE_NUMBER == this.GetData(this.FileSettings.BadgeNumber));
               if (person == null) return false;
               ContactDetail contactDetail = this.DB.ContactDetails.FirstOrDefault(o => o.PERSONs == person);
               contactDetail.Address1 = this.GetData(this.FileSettings.Address1);
               contactDetail.CITY = this.DB.CITies.Where(c => c.NAME == this.GetData(this.FileSettings.City)).FirstOrDefault();
               contactDetail.ZipCode = this.GetData(this.FileSettings.ZipCode);
               contactDetail.Phone = this.GetData(this.FileSettings.Phone);
               this.DB.SubmitChanges();

               ContactDetail contactDetailWork = this.DB.ContactDetails.FirstOrDefault(o => o.PERSONs.ENTERPRISE.ID == person.ENTERPRISE_ID);
               contactDetailWork.Address1 = this.DB.ENTERPRISEs.Where(e => e.NAME == this.GetData(this.FileSettings.Enterprise)).Select(e => e.ADDRESS1).FirstOrDefault();
               contactDetailWork.CITY = this.DB.ENTERPRISEs.Where(e => e.NAME == this.GetData(this.FileSettings.Enterprise)).Select(e => e.CITY).FirstOrDefault();
               contactDetailWork.ZipCode = this.DB.ENTERPRISEs.Where(e => e.NAME == this.GetData(this.FileSettings.Enterprise)).Select(e => e.US_ZIPCODE).FirstOrDefault();
               this.DB.SubmitChanges();

               DETAIL personDetail = this.DB.DETAILs.FirstOrDefault(o => o.PERSON_ID == person.PERSON_ID);
               var personelType = this.DB.PersonnelTypes.Where(t => t.Name == this.GetData(this.FileSettings.TypePersonel)).FirstOrDefault();
               personDetail.PersonnelType = personelType;
               personDetail.PERSONNEL_TYPE = personelType != null ? personelType.Name : "Interne";
               personDetail.idPersonnelType = personelType != null ? personelType.ID : (long?)null;
               personDetail.ENTERPRISE = this.DB.ENTERPRISEs.Where(e => e.NAME == this.GetData(this.FileSettings.Enterprise)).FirstOrDefault();
               personDetail.OCCUPATION_TYPE = this.GetData(this.FileSettings.Occupation);
               personDetail.STARTING_DATE = this.GetDataAsDate(this.FileSettings.StartingDate);
               personDetail.PROFFESION = this.GetData(this.FileSettings.Proffesion);
               personDetail.Qualification = this.DB.Qualifications.Where(q => q.QualificationName == this.GetData(this.FileSettings.Qualification)).FirstOrDefault();
               personDetail.HIREMENT_DATE = this.GetDataAsDate(this.FileSettings.HirementDate);
               this.DB.SubmitChanges();

               WORKING_LOCATION workLocation = this.DB.WORKING_LOCATIONs.FirstOrDefault(o => o.idCompany == person.COMPANY_ID);
               workLocation.idCompany = this.DB.COMPANies.Where(o => o.NAME == this.GetData(this.FileSettings.Company)).Select(o => o.ID).FirstOrDefault();
               this.DB.SubmitChanges();

               person.FIRST_NAME = this.GetData(this.FileSettings.FirstName);
               person.LAST_NAME = this.GetData(this.FileSettings.LastName);
               person.GENDER = this.GetData(this.FileSettings.Gender); // M or F
               person.MAIDEN_NAME = this.GetData(this.FileSettings.MaidenName);
               person.BADGE_NUMBER = this.GetData(this.FileSettings.BadgeNumber);
               person.AGE = (this.GetData(this.FileSettings.Age) != String.Empty) ? (int?)Convert.ToInt32(this.GetData(this.FileSettings.Age)) : null;
               person.BIRTHDATE = this.GetDataAsDate(this.FileSettings.BirthDate);
               person.CIVIL_STATE = this.GetData(this.FileSettings.CivilState);
               person.LANGUAGE = this.GetData(this.FileSettings.Language);
               person.AGE_CLASS = this.GetData(this.FileSettings.Age) != String.Empty ? (from ac in this.DB.age_groups
                                                                                         where ac.min <= (int?)Convert.ToInt32(this.GetData(this.FileSettings.Age))
                                                                               && ac.max > (int?)Convert.ToInt32(this.GetData(this.FileSettings.Age))
                                                                                         select ac.name).FirstOrDefault() : String.Empty;
               person.NATIONALITY = this.GetData(this.FileSettings.Nationality);
               person.personnelCategorieSante = this.GetData(this.FileSettings.CategorieSante);
               person.SOCIAL_SECURITY_NUMBER = this.GetData(this.FileSettings.SocialSecurityNumber).NoWhiteSpace();
               person.ControlKey = this.GetData(this.FileSettings.ControlKey);
               person.COMPANY = this.GetData(this.FileSettings.Company);
               person.ENTERPRISE = (from e in this.DB.ENTERPRISEs
                                    where e.NAME == this.GetData(this.FileSettings.Enterprise)
                                    select e).FirstOrDefault();
               if (DatabaseSvc.Instance.Settings.GeneralSettings.FirstOrDefault().IsMultiColumnPersonImport)
               {
                  var hierarchy = (from o in this.DB.HIERARCHies
                                   let firstID = this.DB.HIERARCHies.FirstOrDefault(h => h.COMPANY.NAME == this.GetData(this.FileSettings.Company) && h.type == 1).HIERARCHY_ID
                                   let secondID = this.DB.HIERARCHies.FirstOrDefault(h => h.COMPANY.NAME == this.GetData(this.FileSettings.Enterprise) && h.type == 2 && h.PARENT_ID == firstID).HIERARCHY_ID
                                   let thirdID = this.DB.HIERARCHies.FirstOrDefault(h => h.COMPANY.NAME == this.GetData(this.FileSettings.Department) && h.type == 3 && h.PARENT_ID == secondID).HIERARCHY_ID
                                   let forthID = this.DB.HIERARCHies.FirstOrDefault(h => h.COMPANY.NAME == this.GetData(this.FileSettings.Sector) && o.type == 3 && h.PARENT_ID == thirdID).HIERARCHY_ID
                                   where o.HIERARCHY_ID == forthID
                                   select o).FirstOrDefault();
                  person.HIERARCHY = hierarchy;
               }
               else
               {
                  person.HIERARCHY = this.GetData(this.FileSettings.Hierarchy) != String.Empty ?
                                                   (from h in this.DB.HIERARCHies
                                                    where h.NAME == this.GetData(this.FileSettings.Hierarchy)
                                                    select h).FirstOrDefault() : null;
               }
               person.ContactDetail = contactDetail;
               person.DETAIL = personDetail;

               person.COMPANY_ID = (from c in this.DB.COMPANies
                                    where c.NAME == this.GetData(this.FileSettings.Company)
                                    select c.ID).FirstOrDefault();
               person.COUNTRY = (from c in this.DB.COUNTRies
                                 where c.NAME == this.GetData(this.FileSettings.Country)
                                 select c).FirstOrDefault();

               person.idContactWorkingLocation = contactDetailWork.id;

               person.idGender = (from c in this.DB.ConfigDropDownLists
                                  where c.Name_fr == this.GetData(this.FileSettings.Gender) && c.ddl_id == 6
                                  select c.id).FirstOrDefault();

               person.idLanguage = (from l in this.DB.Languages
                                    where l.Name == this.GetData(this.FileSettings.Language)
                                    select l.id).FirstOrDefault();

               person.idMaritalStatus = this.DB.ConfigDropDownLists.
                                          Where(o => o.Name_fr == this.GetData(this.FileSettings.CivilState) && o.ddl_id == 5).
                                          Select(o => o.id).FirstOrDefault();

               person.parent_id = this.DB.HIERARCHies.
                                  Where(o => o.NAME == this.GetData(this.FileSettings.Hierarchy)).
                                  Select(o => o.PARENT_ID).FirstOrDefault();

               var typePersonel = this.DB.PersonnelTypes.
                                       Where(o => o.Name == this.GetData(this.FileSettings.TypePersonel)).
                                       Select(o => o.Name).FirstOrDefault();
               person.PERSONNEL_TYPE = typePersonel == String.Empty ? "Interne" : typePersonel;
               person.SOCIAL_WORKER = this.GetData(this.FileSettings.SocialWorker);
               var workingHoursID = (from o in this.DB.working_hours
                                     where o.name == this.GetData(this.FileSettings.WorkingHoursName)
                                     select o.id_working_hours).FirstOrDefault();
               person.WorkingHoursID = workingHoursID == 0 ? new working_hour()
                                       {
                                          code = this.GetData(this.FileSettings.WorkingHoursCode),
                                          name = this.GetData(this.FileSettings.WorkingHoursName),
                                       }.id_working_hours : workingHoursID;
               person.WorkingLocationID = workLocation.WORKING_LOCATION_ID;

               this.DB.SubmitChanges();
            }
            return true;
         }
         catch (Exception ex)
         {
            this.Error = ex.Message;
            return false;
         }
      }
   }
}